﻿/*****************************************************************************/
/* Stored Procedure :: AllGrants                                             */
/*****************************************************************************/
CREATE PROCEDURE [GrantManagement].[AllGrants]
AS  
BEGIN

	SELECT  Grants.OID as 'GrantsOID', 
			Grants.Version as 'GrantsVersion', 
			Grants.CaseID, 
			Grants.ApplicantID, 
			Grants.ValueOfGrant, 
			Grants.CheckNumber, 
			Grants.NameOnCreditCard, 
			Grants.GrantTypeID, 
			Grants.StateID, 
			Grants.PaymentTypeID, 
			Grants.DeliveryMethodID, 
			Grants.DeliverToPersonID, 
			Grants.DeliverToAddressID, 
			Grants.Approver1, 
			Grants.ApprovalDate1, 
			Grants.Approver2, 
			Grants.ApprovalDate2, 
			Grants.DisbursedBy, 
			Grants.DispersalDate, 
			Grants.VerifiedBy, 
			Grants.VerificationDate,
			Grants.UpdatedDate as 'GrantsUpdatedDate',
			Grants.CreatedDate as 'GrantsCreatedDate',

			Cases.OID as 'CaseOID', 
			Cases.Version as 'CaseVersion', 
			Cases.ServiceManID, 
			Cases.CaseVolunteerID, 
			Cases.CaseDirectorID,
			Cases.InjuryDate,

			ServiceMen.OID as 'ServiceMenOID', 
			ServiceMen.Version as 'ServiceMenVersion', 
			ServiceMen.PrimaryAddressID as 'ServiceMenPrimaryAddressID', 
			ServiceMen.PermanentAddressID as 'ServiceMenPermanentAddressID',

			Applicants.OID as 'ApplicantsOID', 
			Applicants.Version as 'ApplicantsVersion', 
			Applicants.KinshipID, 
			Applicants.AddressID as 'ApplicantsAddressID', 

			People.OID as 'PeopleOID', 
			People.Version as 'PeopleVersion', 
			People.FirstName, 
			People.LastName, 
			People.MiddleInitial, 
			People.DOB, 
			People.Sex, 
			People.Phone1, 
			People.Phone2, 
			People.eMailAddress, 

			Users.OID as 'UsersOID', 
			Users.GUID, 
			Users.FullName, 
			Users.UPN, 
			Users.LastSignedIn

	From Grants 
	INNER JOIN Cases On Grants.CaseID = Cases.OID
	INNER JOIN ServiceMen On ServiceMen.OID = Cases.ServiceManID
	INNER JOIN Applicants On Grants.ApplicantID = Applicants.OID
	INNER JOIN People On Applicants.OID = People.OID
	INNER JOIN Users On Users.OID = Cases.CaseVolunteerID

END
GO
